package com.ysf.lib_excel;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.dom4j.io.XMLWriter;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;

public class ExcelReader {

    private static final HashMap<String, ExcelBean> excelDataMap = new LinkedHashMap<>(700);

    public static void main(String[] args) {
        //翻译文件路径
        final String excelFilePath = "D:\\zhou/ParseExcel/cn_en_tr.xlsx";
        //读取翻译文件
        readExcelData(excelFilePath);
        //解析xml文件，并生成指定的语言xml
        parseOriginXml();
    }

    public static void readExcelData(String excelFilePath) {
        try (FileInputStream file = new FileInputStream(excelFilePath); Workbook workbook = new XSSFWorkbook(file)) {
            Sheet sheet = workbook.getSheetAt(0); // 读取第一个工作表
            ExcelBean excelBean;
            for (Row row : sheet) { // 迭代每一行
                excelBean = new ExcelBean();
                for (Cell cell : row) { // 迭代每一列
                    // 根据不同数据类型，以字符串形式输出数据
                    switch (cell.getCellType()) {
                        case STRING:
                            final int columnIndex = cell.getColumnIndex();
                            final String cellVal = cell.getStringCellValue().trim();
                            if (columnIndex == 0) {
                                excelDataMap.put(cellVal, excelBean);
                                excelBean.cn = cellVal;
                            } else if (columnIndex == 1) {
                                excelBean.en = cellVal;
                            } else if (columnIndex == 2) {
                                excelBean.in_Id = cellVal;
                            } else if (columnIndex == 3) {
                                excelBean.tr = cellVal;
                            }else if(columnIndex == 4){
                                excelBean.ar = cellVal;
                            }
//                            System.out.print(cellVal + "\t");
                            break;
                        case NUMERIC:
//                            System.out.print(cell.getNumericCellValue() + "\t");
                            break;
                        case BOOLEAN:
//                            System.out.print(cell.getBooleanCellValue() + "\t");
                            break;
                        case FORMULA:
//                            System.out.print(cell.getCellFormula() + "\t");
                            break;
                        default:
                            break;
                    }
                }
//                System.out.println(); // 换行
            }
//            for (ExcelBean bean : excelDataMap.values()) {
//                System.out.println(bean.toString()); // 换行
//            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 解析原始xml文件
     */
    private static void parseOriginXml() {
        final String xmlFilePath = "D:\\zhou/ParseExcel/test_string.xml";
        Document document;
        try {
            SAXReader saxReader = new SAXReader();
            document = saxReader.read(new File(xmlFilePath));
            final Element rootElement = document.getRootElement();

            final LinkedList<XmlBean> processList = new LinkedList<>();

            XmlBean bean;
            for (Iterator<Element> i = rootElement.elementIterator(); i.hasNext(); ) {
                Element element = i.next();
                bean = new XmlBean();
                bean.key = element.attributeValue("name");
                bean.valueStr = element.getText();
                ExcelBean excelBean = excelDataMap.get(bean.valueStr);
                if (excelBean != null) {
                    bean.en = excelBean.en;
                    bean.tr = excelBean.tr;
                    bean.ar = excelBean.ar;
                    bean.in_Id = excelBean.in_Id;
                    processList.addFirst(bean);
                } else {
                    //未匹配
                    bean.en = bean.valueStr;
                    bean.tr = bean.valueStr;
                    bean.ar = bean.valueStr;
                    bean.in_Id = bean.valueStr;
                    processList.addLast(bean);
                }
            }

            for (XmlBean xb : processList) {
                System.out.println("data=" + xb);
            }
            //生成匹配的英文
            generateEnXml(processList);
            //生成匹配的土耳其语
            generateTrXml(processList);
            //阿拉伯语
            generateArXml(processList);
            //印尼
            generateInXml(processList);

            //印尼
            generateZnXml(processList);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //英文
    private static void generateEnXml(LinkedList<XmlBean> processList) {
        final String xmlEnFilePath = "D:\\zhou/ParseExcel/strings_en.xml";
        final Document document = DocumentHelper.createDocument();
        final Element resElement = document.addElement("resources");
        for (XmlBean bean : processList) {
            Element element = resElement.addElement("string");
            element.addAttribute("name", bean.key);
            if (bean.en != null) {
                element.addText(bean.en);
            } else {
                element.addText(bean.valueStr);
            }
        }
        try {
            final XMLWriter xmlWriter = new XMLWriter(new FileWriter((xmlEnFilePath)));
            xmlWriter.write(document);
            xmlWriter.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    //土耳其
    private static void generateTrXml(LinkedList<XmlBean> processList) {
        final String xmlTrFilePath = "D:\\zhou/ParseExcel/strings_tr.xml";
        final Document document = DocumentHelper.createDocument();
        final Element resElement = document.addElement("resources");
        for (XmlBean bean : processList) {
            Element element = resElement.addElement("string");
            element.addAttribute("name", bean.key);
            if (bean.tr != null) {
                element.addText(bean.tr);
            } else {
                element.addText(bean.valueStr);
            }
        }
        try {
            final XMLWriter xmlWriter = new XMLWriter(new FileWriter((xmlTrFilePath)));
            xmlWriter.write(document);
            xmlWriter.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    //阿拉伯
    private static void generateArXml(LinkedList<XmlBean> processList) {
        final String xmlTrFilePath = "D:\\zhou/ParseExcel/strings_ar.xml";
        final Document document = DocumentHelper.createDocument();
        final Element resElement = document.addElement("resources");
        for (XmlBean bean : processList) {
            Element element = resElement.addElement("string");
            element.addAttribute("name", bean.key);
            if (bean.ar != null) {
                element.addText(bean.ar);
            } else {
                element.addText(bean.valueStr);
            }
        }
        try {
            final XMLWriter xmlWriter = new XMLWriter(new FileWriter((xmlTrFilePath)));
            xmlWriter.write(document);
            xmlWriter.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    //印尼
    private static void generateInXml(LinkedList<XmlBean> processList) {
        final String xmlTrFilePath = "D:\\zhou/ParseExcel/strings_in.xml";
        final Document document = DocumentHelper.createDocument();
        final Element resElement = document.addElement("resources");
        for (XmlBean bean : processList) {
            Element element = resElement.addElement("string");
            element.addAttribute("name", bean.key);
            if (bean.in_Id != null) {
                element.addText(bean.in_Id);
            } else {
                element.addText(bean.valueStr);
            }
        }
        try {
            final XMLWriter xmlWriter = new XMLWriter(new FileWriter((xmlTrFilePath)));
            xmlWriter.write(document);
            xmlWriter.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    //香港
    private static void generateZnXml(LinkedList<XmlBean> processList) {
        final String xmlTrFilePath = "D:\\zhou/ParseExcel/strings_zn.xml";
        final Document document = DocumentHelper.createDocument();
        final Element resElement = document.addElement("resources");
        for (XmlBean bean : processList) {
            Element element = resElement.addElement("string");
            element.addAttribute("name", bean.key);
            if (bean.in_Id != null) {
                element.addText(bean.in_Id);
            } else {
                element.addText(bean.valueStr);
            }
        }
        try {
            final XMLWriter xmlWriter = new XMLWriter(new FileWriter((xmlTrFilePath)));
            xmlWriter.write(document);
            xmlWriter.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}